package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.StudentService;

public class PaymenterDao {
	private int pageSize=8;
	/**
	 * 统计表加入一条数据
	 */
	public boolean addTotalData(int stuId,int allotId,int totalTerm,int ifLoan,double totalMoney,double totalNoMoney) throws SQLException {
		String sql="insert into total (total_student_id,total_staff_id,total_term,total_if_loan,total_money,total_no_money) values (?,?,?,?,?,?)";
		return Jdbc.getInstace().executeUpdate(sql,stuId,allotId,totalTerm,ifLoan,totalMoney,totalNoMoney);
	}
	/**
	 * 带条件分页查询
	 * @param type
	 * @param page
	 * @param userName
	 * @param dept
	 * @param tel
	 * @return
	 * @throws SQLException
	 */
	public List<StudentService> getPageStudentListByType(int page, String studentName)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from student left join staff on student.student_allot_id=staff.staff_id ";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, studentName);
		sql += " order by student_id desc limit ?,?";
		params.add(start);
		params.add(pageSize);
		return queryUserList(sql, params.toArray());
	}
	/**
	 * 搜索总页数
	 */
	public int getTotalPage(String studentName) throws SQLException {
		String sql = "select count(*) total from student left join staff on student.student_allot_id=staff.staff_id";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, studentName);
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer total = Integer.parseInt(map.get("total").toString());
		return (int) Math.ceil(total.doubleValue() / pageSize);
	}
	
	private String buildSqlAndParams(List<Object> params, String sql,String studentName) {
		if (!StringUtils.isNullOrEmpty(studentName)) {
			sql += " where student_name = ? ";
			params.add(studentName );
		}

		return sql;
	}
	
	private List<StudentService> queryUserList(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> listData = Jdbc.getInstace().executeQueryForList(sql, params);
		List<StudentService> studentList = null;
		if (listData != null && listData.size() > 0) {
			studentList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				studentList.add(buildStudent(data));
			}
		}
		return studentList;
	}
	
	private StudentService buildStudent(Map<String, Object> data) {
		StudentService student = new StudentService();
		student.setStudentId((Integer) data.get("student_id"));
		student.setStudentSex((Integer) data.get("student_sex"));
		student.setStudentPhone((String) data.get("student_phone"));
		student.setStudentAllotId((Integer) data.get("student_allot_id"));
		student.setStudentName((String) data.get("student_name"));
		student.setStudentStaffName((String) data.get("staff_name"));
		return student;
	}
}
